package com.fwamgic.spark;

import java.sql.*;

public class OracleUtils {
    //数据库连接地址
    private static String url = "jdbc:oracle:thin:@localhost:1521:helowin";
    //用户名
    private static String username = "system";
    //密码
    private static String password = "oracle";
    //驱动名称
    private static String jdbcName = "oracle.jdbc.OracleDriver";

    private static Connection connection;

    private static PreparedStatement ps;

    private static ResultSet rs;

    /*获取数据库连接 */
    public static Connection getCon() {
        try {
            Class.forName(jdbcName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        Connection con = null;
        try {
            con = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    /*关闭数据库连接*/
    public static void closeCon(Connection con) throws SQLException {
        if (con != null)
            con.close();
    }

    /*插入*/
    public static int insert() throws Exception {
        int result = 0;
        Connection connection = getCon();
        String sql = "insert into \"student\" (\"id\",\"name\",\"password\",\"insert_time\",\"update_time\") values(?,?,?,?,?)";
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            ps.setLong(1, 1L);
            ps.setString(2, "fwmagic");
            ps.setString(3, "123456");
            ps.setDate(4, new Date(1000L));
            ps.setDate(5, new Date(2000L));
            result = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeCon(connection);
        }
        return result;
    }

    /*查询*/
    public static void select() throws SQLException {
        connection = getCon();
        String sql = "select \"id\",\"name\",\"password\" from \"student\"";
        try {
            ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int id = rs.getInt(1);
                String name = rs.getString(2);
                String password = rs.getString(3);
                System.out.println("ID:" + id + " NAME:" + name + " Password:" + password);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeCon(connection);
        }
    }


    /*修改*/
    public static int update() throws SQLException {
        connection = getCon();
        String sql = "update \"student\" set \"name\" = ?,\"password\" = ? where \"id\" = ?";
        int result = 0;
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1, "fwmagic_new");
            ps.setString(2, "654321");
            ps.setLong(3, 1L);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeCon(connection);
        }

        return result;

    }

    /*删除 */
    public static int delete() throws SQLException {
        int result = 0;
        connection = getCon();
        String sql = "delete from \"student\" where \"id\" = ?";
        try {
            ps = connection.prepareStatement(sql);
            ps.setInt(1, 1);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeCon(connection);
        }
        return result;
    }

    public static void main(String[] args) {
        try {
            insert();
            select();
            //delete();
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("数据库连接失败");
        }
    }


}
